﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CodeGenerator.interfaces;

namespace CodeGenerator
{
    public class TemplateProvider
    {
        const string CONST_TEMPLATE_CLASS_PROPERTY_FILENAME = @"CodeGenerationTemplates\ClassProperty.tpl";//{PropertyType}{PropertyName}{PropertyLocalVariableName}
        const string CONST_TEMPLATE_CLASS_PROPERTY_LOCAL_VARIABLE_FILENAME = @"CodeGenerationTemplates\ClassPropertyLocalVariable.tpl";//{PropertyType} {PropertyName}{PropertyLocalVariableName}{TableName}{ColumnName}
        const string CONST_TEMPLATE_CLASS_FILENAME = @"CodeGenerationTemplates\Class.tpl";
        const string CONST_TEMPLATE_CLASS_REVERT_METHOD_FILENAME = @"CodeGenerationTemplates\ClassMethodRevert.tpl";
        const string CONST_TEMPLATE_DATA_CLASS_PROPERTY_FILENAME = @"CodeGenerationTemplates\DataClassProperty.tpl";//{PropertyType}{PropertyName}{PropertyLocalVariableName}
        const string CONST_TEMPLATE_DATA_CLASS_PROPERTY_LOCAL_VARIABLE_FILENAME = @"CodeGenerationTemplates\ClassPropertyLocalVariable.tpl";//{PropertyType} {PropertyName}{PropertyLocalVariableName}{TableName}{ColumnName}
        const string CONST_TEMPLATE_DATA_CLASS_FILENAME = @"CodeGenerationTemplates\DataClass.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_FILENAME = @"CodeGenerationTemplates\DataAccessClass.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_PRIVATE_VARIABLES_FILENAME = @"CodeGenerationTemplates\DataAccessClassPrivateVariables.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_LOADER_FILENAME = @"CodeGenerationTemplates\DataAccessClassDataLoader.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_CREATE_FROM_DATAROW_METHOD_FILENAME = @"CodeGenerationTemplates\DataAccessClassCreateFromDataRowMethod.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_UPDATE_METHOD_FILENAME_SQLite = @"CodeGenerationTemplates\DataAccessClassUpdateMethodSqlite.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_UPDATE_METHOD_FILENAME_SqlServer = @"CodeGenerationTemplates\DataAccessClassUpdateMethodSqlServer.tpl";
        
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_INSERT_METHOD_FILENAME_SQLite = @"CodeGenerationTemplates\DataAccessClassInsertMethodSQLite.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_INSERT_METHOD_FILENAME_SQLServer = @"CodeGenerationTemplates\DataAccessClassInsertMethodSQLServer.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_GETALL_METHOD_FILENAME = @"CodeGenerationTemplates\DataAccessClassGetAllMethod.tpl";
        const string CONST_TEMPLATE_DATA_ACCESS_CLASS_REMOVE_METHODS_FILENAME = @"CodeGenerationTemplates\DataAccessClassRemoveMethods.tpl";

        
        const string CONST_TEMPLATE_PROJECT_FILENAME = @"CodeGenerationTemplates\Project.tpl";
        const string CONST_TEMPLATE_PROJECT_SAMPLE_FILENAME = @"CodeGenerationTemplates\ProjectSample.tpl";
        const string CONST_TEMPLATE_PROJECT_SAMPLE_PROGRAM_CS_FILENAME = @"CodeGenerationTemplates\ProjectSample_Program.cs.tpl";
        const string CONST_TEMPLATE_PROJECT_SAMPLE_MAINFORM_CS_FILENAME = @"CodeGenerationTemplates\ProjectSample_MainForm.cs.tpl";
        const string CONST_TEMPLATE_PROJECT_SAMPLE_MAINFORM_RESX_FILENAME = @"CodeGenerationTemplates\ProjectSample_MainForm.resx.tpl";
        const string CONST_TEMPLATE_PROJECT_SAMPLE_SOLUTION_FILENAME = @"CodeGenerationTemplates\Solution.tpl";


        const string CONST_TEMPLATE_ASSEMBLY_INFO_FILENAME = @"CodeGenerationTemplates\AssemblyInfo.tpl";
        const string CONST_TEMPLATE_STORED_PROCEDURE_INSERT = @"CodeGenerationTemplates\StoredProcedureInsertSqlServer.tpl";
        const string CONST_TEMPLATE_STORED_PROCEDURE_UPDATE = @"CodeGenerationTemplates\StoredProcedureUpdateSqlServer.tpl";
        const string CONST_TEMPLATE_STORED_PROCEDURE_DELETE = @"CodeGenerationTemplates\StoredProcedureDeleteSqlServer.tpl";
        

        
        

        ////DataAccessClassUpdateMethod.tpl

        const string CONST_TEMPLATE_FOREIGN_KEY_PROPERTY_FILENAME = @"CodeGenerationTemplates\ClassForeignKeyProperty.tpl";//{PropertyType}{PropertyName}{PropertyLocalVariableName}
        const string CONST_TEMPLATE_CONSTRUCTOR_FILENAME = @"CodeGenerationTemplates\ClassConstructor.tpl";
        const string CONST_TEMPLATE_SQLITECONNECTOR = @"CodeGenerationTemplates\SQLiteConnector.tpl";
        const string CONST_TEMPLATE_SQLSERVERECONNECTOR = @"CodeGenerationTemplates\SQLServerConnector.tpl";
        

        //private readonly string _templatesPath;

        public TemplateProvider(/*string TemplatesPath*/)
        {
            //_templatesPath = TemplatesPath;
            
            //if(!Directory.Exists(_templatesPath))
            //    throw new ApplicationException("Templates folder dont exists!");
        }


        public string GetClassPropertySourceCode(IProperty Prop)
        {
            string TemplatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_CLASS_PROPERTY_FILENAME);
            TemplatePlainText = TemplatePlainText.Replace("{PropertyType}", Prop.DatabaseColumn.DataType.Name);
            TemplatePlainText = TemplatePlainText.Replace("{PropertyName}", Prop.PropertyName);
            //TemplatePlainText = TemplatePlainText.Replace("{PropertyLocalVariableName}", Prop.DataClassLocalVariableName);
            return TemplatePlainText;
        }

        public string GetDataClassPropertySourceCode(IProperty Prop)
        {
            string TemplatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_CLASS_PROPERTY_FILENAME);
            TemplatePlainText = TemplatePlainText.Replace("{PropertyType}", Prop.DatabaseColumn.DataType.Name);
            TemplatePlainText = TemplatePlainText.Replace("{PropertyName}", Prop.PropertyName);
            TemplatePlainText = TemplatePlainText.Replace("{PropertyLocalVariableName}", Prop.DataClassLocalVariableName);
            return TemplatePlainText;
        }

        public string GetForeignKeyPropertySourceCode(IForeignKey ForeignKey, IGeneratorModel Model)
        {
            
            StringBuilder ForeignConstructorParameters = new StringBuilder();
            StringBuilder PropertyLocalVariableNamesAsignation = new StringBuilder();
            //Necesito sacar la property que corresponde a la columna del foreign key column para pedirle el localproperty name.

            IGeneratorClass clas = (from n in Model.Classes
                                where n.DatabaseTable.TableName == ForeignKey.TableName
                                select n).Single();

            IGeneratorClass ForeignClass = (from n in Model.Classes
                                    where n.DatabaseTable.TableName == ForeignKey.ForeignTableName
                                    select n).Single();
            


            foreach (IDataBaseColumnPair fkey in ForeignKey.Columns)
            {
                IDataBaseColumnPair fkey1 = fkey;
                IDataBaseColumnPair p1 = fkey;
                IProperty localProperty = (from IProperty x in clas.Properties where x.DatabaseColumn.ColumnName == p1.LocalColumn.ColumnName select x).Single();
                IProperty foreignProperty = (from IProperty x in ForeignClass.Properties where x.DatabaseColumn.ColumnName == fkey1.RemoteColumn.ColumnName select x).Single();
                ForeignConstructorParameters.Append("   _ownData." + localProperty.PropertyName + ", ");
                //PropertyLocalVariableNamesAsignation.Append("   _ownData." + ppt.PropertyName + " = value." + ppt.PropertyName + ";");
                PropertyLocalVariableNamesAsignation.Append("   _ownData." + localProperty.PropertyName + " = value." + foreignProperty.PropertyName + ";");
            }

            if (ForeignKey.Columns.Count > 0)
                ForeignConstructorParameters.Remove(ForeignConstructorParameters.Length - 2, 2);

            string TemplatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_FOREIGN_KEY_PROPERTY_FILENAME);
            string className = GetClassNameByTableName(ForeignKey.ForeignTableName, Model);
            TemplatePlainText = TemplatePlainText.Replace("{PropertyType}", className);
            string indexNameToUse = "";
            
            if (className == clas.ClassName)
            {
                indexNameToUse = "_" + ForeignKey.IndexName + "/*<-FK name to avoid compilation error*/";
            }
            
            TemplatePlainText = TemplatePlainText.Replace("{IndexName}", indexNameToUse);
            TemplatePlainText = TemplatePlainText.Replace("{PropertyName}", className);
            TemplatePlainText = TemplatePlainText.Replace("{PropertyLocalVariableName}", ForeignKey.LocalVariableName);
            TemplatePlainText = TemplatePlainText.Replace("{ForeignConstructorParameters}", ForeignConstructorParameters.ToString());
            TemplatePlainText = TemplatePlainText.Replace("{PropertyLocalVariableNamesAsignation}", PropertyLocalVariableNamesAsignation.ToString());
            
            return TemplatePlainText;
        }

        private string GetClassNameByTableName(string ForeignTableName, IGeneratorModel Model)
        {
             return (from n in Model.Classes
                    where n.DatabaseTable.TableName.Equals(ForeignTableName, StringComparison.OrdinalIgnoreCase)
                         select n.ClassName).Single();
            
        }

        public string GetClassPropertyLocalVariableSourceCode(IProperty Prop,IGeneratorModel Model)
        {
            //     {PropertyLocalVariableType}     {PropertyLocalVariableName}     {PropertyLocalVariableInitValue}{TableName}{ColumnName}
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_CLASS_PROPERTY_LOCAL_VARIABLE_FILENAME);
            templatePlainText = templatePlainText.Replace("{PropertyName}", Prop.PropertyName);
            
            //{PropertyName}
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableType}", Prop.DatabaseColumn.DataType.Name);
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableName}", Prop.DataClassLocalVariableName);
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableInitValue}", GetDefaultValue(Prop.DatabaseColumn.DataType));
             templatePlainText = templatePlainText.Replace("{TableName}", Prop.ContainerTable.TableName);
             templatePlainText = templatePlainText.Replace("{ColumnName}", Prop.DatabaseColumn.ColumnName);
           
            
            
            return templatePlainText;
        }

        public static string GetDefaultValue(Type type)
        {
            object obj = type.IsValueType ? Activator.CreateInstance(type) : obj = null;
            if (obj == null)
            {
                if (type == typeof(String) ||
               type == typeof(Guid))
                //DatabaseType == typeof(Binary))
                {
                    return "\"\"";
                }
                return "null";
            }
            if (type == typeof(DateTime))
            {
                return "DateTime.MinValue";
            }
                
            if (
                type == typeof(Int16) ||
                type == typeof(Int32) ||
                type == typeof(Int64) ||
                type == typeof(Byte) ||
                type == typeof(Double) ||
                type == typeof(Single) ||
                type == typeof(Decimal))
            {
                return "-1";
            }
            if (type == typeof(Boolean) || type == typeof(bool))
            {
                return "false";
            }
                
            return obj.ToString();
        }
        /*
         sbPrivateVariables.ToString(), 
         sbPublicProperties,
         sbForeignKeysProperties,
         _model
         */
        public String GetClassSourceCode(string ClassName, 
                                         IGeneratorModel Model,
                                         string StrConstructors,
                                         string StrPrivateVariables, 
                                         string StrPublicProperties, 
                                         string StrForeignKeysProperties, 
                                         string StrForeignKeysPrivateVariables)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_CLASS_FILENAME);
            templatePlainText = templatePlainText.Replace("{namespace}", Model.NameSpace);
            templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
            templatePlainText = templatePlainText.Replace("{PrivateVariables}", StrPrivateVariables);
            templatePlainText = templatePlainText.Replace("{Constructors}", StrConstructors);
            templatePlainText = templatePlainText.Replace("{PublicProperties}", StrPublicProperties);
            templatePlainText = templatePlainText.Replace("{ForeignKeysPrivateVariables}", StrForeignKeysPrivateVariables);
            templatePlainText = templatePlainText.Replace("{ForeignKeysProperties}", StrForeignKeysProperties);
            return templatePlainText;
        }

        public String GetDataClassSourceCode(string ClassName,
                                 IGeneratorModel Model,
                                 string StrConstructors,
                                 string StrPrivateVariables,
                                 string StrPublicProperties,
                                 string StrForeignKeysProperties,
                                 string StrForeignKeysPrivateVariables)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_CLASS_FILENAME);
            templatePlainText = templatePlainText.Replace("{namespace}", Model.NameSpace);
            templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
            templatePlainText = templatePlainText.Replace("{PrivateVariables}", StrPrivateVariables);
            templatePlainText = templatePlainText.Replace("{PublicProperties}", StrPublicProperties);
            return templatePlainText;
        }

        //UpdateMethodSourceCode
        public string GetDataAccessClassSourceCode(string ClassName, IGeneratorModel Model, string ConstructorsSourceCode, string PrivateVariablesSourceCode, string PublicPropertiesSourceCode, string ForeignKeysPropertiesSourceCode, string ForeignKeysPrivateVariablesSourceCode, string DataLoadersSourceCode, string CreateFromDataRowSourceCode, string UpdateMethodSourceCode, string ColumnsNames, string InsertMethodSourceCode, string GetAllMethod, string RemoveMethods, string DataBaseSpecificUsings)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_ACCESS_CLASS_FILENAME);
            templatePlainText = templatePlainText.Replace("{DataBaseSpecificUsings}", DataBaseSpecificUsings);
            templatePlainText = templatePlainText.Replace("{namespace}", Model.NameSpace);
            templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
            templatePlainText = templatePlainText.Replace("{PrivateVariables}", PrivateVariablesSourceCode);
            templatePlainText = templatePlainText.Replace("{PublicProperties}", PublicPropertiesSourceCode);
            templatePlainText = templatePlainText.Replace("{DataLoaders}", DataLoadersSourceCode);
            templatePlainText = templatePlainText.Replace("{CreateFromDataRowSourceCode}", CreateFromDataRowSourceCode);
            templatePlainText = templatePlainText.Replace("{UpdateMethodSourceCode}", UpdateMethodSourceCode);
            templatePlainText = templatePlainText.Replace("{ColumnNames}", ColumnsNames);
            templatePlainText = templatePlainText.Replace("{InsertMethodSourceCode}", InsertMethodSourceCode);
            templatePlainText = templatePlainText.Replace("{GetAllMethod}", GetAllMethod);
            templatePlainText = templatePlainText.Replace("{RemoveMethods}", RemoveMethods);
            
            
            return templatePlainText;
        }
        
        public string GetForeignKeyLocalVariableSourceCode(IForeignKey ForeignKey, IGeneratorModel Model)
        {
            //     {PropertyLocalVariableType}     {PropertyLocalVariableName}     {PropertyLocalVariableInitValue}{TableName}{ColumnName}
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_CLASS_PROPERTY_LOCAL_VARIABLE_FILENAME);
            templatePlainText = templatePlainText.Replace("{PropertyName}", ForeignKey.ForeingClass.ClassName);

            //{PropertyName}
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableType}", ForeignKey.ForeingClass.ClassName);
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableName}", ForeignKey.LocalVariableName);
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableInitValue}", "null");
            templatePlainText = templatePlainText.Replace("{TableName}", "FK relation (" + ForeignKey.IndexName + ") to " + ForeignKey.ForeignTableName);
            
            
            //Handle multiple columns foreign keys
            if(ForeignKey.Columns.Count == 1)
                templatePlainText = templatePlainText.Replace("{ColumnName}",ForeignKey.Columns[0].RemoteColumn.ColumnName);
            else
            {
                string columnsList = "";
                foreach (IDataBaseColumnPair dataBaseColumnPair in ForeignKey.Columns)
                {
                    columnsList += dataBaseColumnPair.RemoteColumn.ColumnName + ", ";
                }
                columnsList = columnsList.Substring(0, columnsList.Length - 2);
                templatePlainText = templatePlainText.Replace("{ColumnName}", columnsList);

            }



            return templatePlainText;  
        }

        public string GetDataAccessClassLoaderSourceCode(IConstructor Constructor, IGeneratorModel Model, DatabaseEngines databaseEngine)
        {

            //{ClassName}{LoaderName}{InputParams}{SelectColumnsList}{TableName}{WhereCondition}
            string _whereCondition = "";
            string _templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_ACCESS_CLASS_LOADER_FILENAME);

            var x = (from IGeneratorClass i in Model.Classes
                     where i.DatabaseTable.TableName == Constructor.TableName
                     select i).Single();

            string selectColumnsList = "";
            foreach (IDatabaseColumn databaseColumn in x.DatabaseTable.Columns)
            {
                selectColumnsList += databaseColumn.ColumnName + ", ";
            }
            selectColumnsList = selectColumnsList.Substring(0,selectColumnsList.Length - 2);

            _templatePlainText = _templatePlainText.Replace("{ClassName}", x.ClassName);
            _templatePlainText = _templatePlainText.Replace("{DalLoaderName}", Constructor.DalLoaderName);
            _templatePlainText = _templatePlainText.Replace("{SelectColumnsList}", selectColumnsList);
            if(databaseEngine == DatabaseEngines.SqlServer)
                _templatePlainText = _templatePlainText.Replace("{TableName}", "[" + x.DatabaseTable.TableName + "]");
            else
                _templatePlainText = _templatePlainText.Replace("{TableName}", x.DatabaseTable.TableName);
            
            if (Constructor.Columns.Count == 1)
            {
                //Busco la property para sacarle el DataClassLocalVariableName
                IProperty constructorColumnProperty = (from IProperty o in x.Properties
                               where o.DatabaseColumn.ColumnName == Constructor.Columns[0].ColumnName
                               select o).Single();

                string parameterName = Common.Formatting.ToString_OneTwoThree(constructorColumnProperty.DatabaseColumn.ColumnName);
                _templatePlainText = _templatePlainText.Replace("{InputParams}", constructorColumnProperty.DatabaseColumn.DataType.Name + " " + parameterName);

                _whereCondition += constructorColumnProperty.DatabaseColumn.ColumnName + " = " + GetEnclosureMarksByType(constructorColumnProperty.DatabaseColumn.DataType) + "\" + " + parameterName + " + \"" + GetEnclosureMarksByType(constructorColumnProperty.DatabaseColumn.DataType) ;
                _templatePlainText = _templatePlainText.Replace("{WhereCondition}", _whereCondition);
            }
            else
            {
                string constructorParametersList = "";
                foreach (IDatabaseColumn col in Constructor.Columns)
                {
                    IProperty zProp = (from IProperty o in x.Properties
                                       where o.DatabaseColumn.ColumnName == col.ColumnName
                                       select o).Single();
                    constructorParametersList += zProp.DatabaseColumn.DataType.Name + " " + Common.Formatting.ToString_OneTwoThree(zProp.DatabaseColumn.ColumnName) + ", ";

                    string parameterName = Common.Formatting.ToString_OneTwoThree(zProp.DatabaseColumn.ColumnName);
                    
                    _whereCondition += zProp.DatabaseColumn.ColumnName + " = " + GetEnclosureMarksByType(zProp.DatabaseColumn.DataType) + "\" + " + parameterName + " + \"" + GetEnclosureMarksByType(zProp.DatabaseColumn.DataType) + " AND ";

                }
                constructorParametersList = constructorParametersList.Substring(0, constructorParametersList.Length - 2);
                _whereCondition = _whereCondition.Substring(0, _whereCondition.Length - 5);

                _templatePlainText = _templatePlainText.Replace("{InputParams}", constructorParametersList);


                _templatePlainText = _templatePlainText.Replace("{WhereCondition}", _whereCondition);

            }
            return _templatePlainText;
        }

        public static string GetEnclosureMarksByType(Type DatabaseType)
        {
            if (
                DatabaseType == typeof (Int16) ||
                DatabaseType == typeof (Int32) ||
                DatabaseType == typeof (Int64) ||
                DatabaseType == typeof (Byte) ||
                DatabaseType == typeof (Double) ||
                DatabaseType == typeof (Single) ||
                DatabaseType == typeof (Decimal))
            {
                return "";
            }
            if (DatabaseType == typeof (String) ||
                DatabaseType == typeof (Guid))
                //DatabaseType == typeof(Binary))
            {
                return "'";
            }

            if (DatabaseType == typeof (Boolean))
            {
                return "";
            }

            if (DatabaseType == typeof (DateTime))
            {
                return "'";
            }
            //log something here
            return "";
        }

        public string GetClassConstructorSourceCode(IConstructor Constructor, IGeneratorModel Model)
        {
            //{ClassName} {ConstructorParametersList}{InitializationForDalConstructor}
            string _InitializationForDalConstructor = "";
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_CONSTRUCTOR_FILENAME);
            
            var x = (from IGeneratorClass i in Model.Classes
                    where i.DatabaseTable.TableName == Constructor.TableName
                    select i).Single();

            templatePlainText = templatePlainText.Replace("{ClassName}", x.ClassName);
            templatePlainText = templatePlainText.Replace("{DalLoaderName}", Constructor.DalLoaderName);

            if (Constructor.Columns.Count == 1)
            {
                //Busco la property para sacarle el DataClassLocalVariableName
                IProperty z = (from IProperty o in x.Properties
                        where o.DatabaseColumn.ColumnName == Constructor.Columns[0].ColumnName
                        select o).Single();

                templatePlainText = templatePlainText.Replace("{ConstructorParametersList}", z.DatabaseColumn.DataType.Name + " " + Common.Formatting.ToString_OneTwoThree(z.DatabaseColumn.ColumnName));
                _InitializationForDalConstructor += Common.Formatting.ToString_OneTwoThree(z.DatabaseColumn.ColumnName);
                templatePlainText = templatePlainText.Replace("{InitializationForDalConstructor}", _InitializationForDalConstructor);
            }
            else
            {
                string ConstructorParametersList = "";
                foreach (IDatabaseColumn col in Constructor.Columns)
                {
                    IProperty zProp = (from IProperty o in x.Properties
                                  where o.DatabaseColumn.ColumnName == col.ColumnName
                                  select o).Single();
                    ConstructorParametersList += zProp.DatabaseColumn.DataType.Name + " " + Common.Formatting.ToString_OneTwoThree(zProp.DatabaseColumn.ColumnName) + ", ";

                    _InitializationForDalConstructor += Common.Formatting.ToString_OneTwoThree(zProp.DatabaseColumn.ColumnName) + ", ";

                }
                ConstructorParametersList = ConstructorParametersList.Substring(0, ConstructorParametersList.Length - 2);
                _InitializationForDalConstructor = _InitializationForDalConstructor.Substring(0, _InitializationForDalConstructor.Length - 2);

                templatePlainText = templatePlainText.Replace("{ConstructorParametersList}", ConstructorParametersList);
               
                
                templatePlainText = templatePlainText.Replace("{InitializationForDalConstructor}", _InitializationForDalConstructor);
        
            }


            return templatePlainText;

        }
        
        public string GetDataAccessClassCreateFromDataRowMethodSourceCode(IGeneratorClass Prop, IGeneratorModel Model)
        {
            //{ClassName}{PropertiesAssignation}
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_ACCESS_CLASS_CREATE_FROM_DATAROW_METHOD_FILENAME);
            templatePlainText = templatePlainText.Replace("{ClassName}", Prop.ClassName);
            
            return templatePlainText;
        }

        public string GetDataAccessClassUpdateMethodSourceCodeSQLite(string ClassName, IGeneratorModel Model, string WhereCondition, string TableName, string PropertiesNames)
        {
            //{ClassName}{PropertiesAssignation}
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_ACCESS_CLASS_UPDATE_METHOD_FILENAME_SQLite);
            templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
            templatePlainText = templatePlainText.Replace("{WhereCondition}", WhereCondition);
            templatePlainText = templatePlainText.Replace("{TableName}", TableName);
            templatePlainText = templatePlainText.Replace("{PropertiesNames}", PropertiesNames);
            return templatePlainText;
        }
        public string GetDataAccessClassUpdateMethodSourceCodeSqlServer(string ClassName, string TableName, string ParametersAssignation)
        {
            //{ClassName}{PropertiesAssignation}
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_ACCESS_CLASS_UPDATE_METHOD_FILENAME_SqlServer);
            templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
            templatePlainText = templatePlainText.Replace("{ParametersAssignation}", ParametersAssignation);
            return templatePlainText;
        }
        public string GetDataClassPropertyLocalVariableSourceCode(IProperty Prop, IGeneratorModel Model)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_CLASS_PROPERTY_LOCAL_VARIABLE_FILENAME);
            templatePlainText = templatePlainText.Replace("{PropertyName}", Prop.PropertyName);
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableType}", Prop.DatabaseColumn.DataType.Name);
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableName}", Prop.DataClassLocalVariableName);
            templatePlainText = templatePlainText.Replace("{PropertyLocalVariableInitValue}", GetDefaultValue(Prop.DatabaseColumn.DataType));
            templatePlainText = templatePlainText.Replace("{TableName}", Prop.ContainerTable.TableName);
            templatePlainText = templatePlainText.Replace("{ColumnName}", Prop.DatabaseColumn.ColumnName);
            return templatePlainText;
        }

        
        public string GetDataAccessClassPrivateVariables()
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_ACCESS_CLASS_PRIVATE_VARIABLES_FILENAME);
            //templatePlainText = templatePlainText.Replace("{ColumnName}", Prop.DatabaseColumn.ColumnName);
            return templatePlainText;
        }

        public string GetSQLConnectorSourceCode(IGeneratorModel Model, DatabaseEngines databaseEngine)
        {
            string templatePlainText = "";
            switch (databaseEngine)
            {
                case DatabaseEngines.SqlLite:
                    {
                        templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_SQLITECONNECTOR);
                        break;
                    }
            case DatabaseEngines.SqlServer:
                    {
                        templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_SQLSERVERECONNECTOR);
                        break;
                    }
                default:
                    throw new ArgumentOutOfRangeException("databaseEngine not found");
            }
            
            templatePlainText = templatePlainText.Replace("{namespace}", Model.NameSpace);
            return templatePlainText;
        }

        public string GetDataAccessClassInsertMethodSourceCodeSqlIte(string ClassName, IGeneratorModel Model, string WhereCondition, string TableName, string PropertiesNames, string ParameterNames)
        {
            //{ClassName}{PropertiesAssignation}
            string templatePlainText = "";
                        templatePlainText = Common.Templating.GetTemplatePlainText(
                                CONST_TEMPLATE_DATA_ACCESS_CLASS_INSERT_METHOD_FILENAME_SQLite);
                        templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
                        templatePlainText = templatePlainText.Replace("{WhereCondition}", WhereCondition);
                        templatePlainText = templatePlainText.Replace("{TableName}", TableName);
                        templatePlainText = templatePlainText.Replace("{PropertiesNames}", PropertiesNames);
                        templatePlainText = templatePlainText.Replace("{ParametersNames}", ParameterNames);
            
            return templatePlainText;
        }


        public string GetDataAccessClassInsertMethodSourceCodeSqlServer(string ClassName, string TableName, string ParametersAssignation, string AutoGeneratedIdsToDataClassAssignation)
        {
            //{ClassName}{PropertiesAssignation}
            string templatePlainText = "";
            templatePlainText = Common.Templating.GetTemplatePlainText(
            CONST_TEMPLATE_DATA_ACCESS_CLASS_INSERT_METHOD_FILENAME_SQLServer);
            templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
            templatePlainText = templatePlainText.Replace("{TableName}", TableName);
            templatePlainText = templatePlainText.Replace("{ParametersAssignation}", ParametersAssignation);
            templatePlainText = templatePlainText.Replace("{AutoGeneratedIdsToDataClassAssignation}	", AutoGeneratedIdsToDataClassAssignation);

            return templatePlainText;
        }

        public string GetDataAccessClassGenericMethodParameterToDbCommand(int IndexPosition, string ParamName, string TypeName, string Value, string spacer)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(spacer + "parameters[" + IndexPosition  + "].ParameterName = \"" + ParamName + "\";");
            sb.AppendLine(spacer + "parameters[" + IndexPosition + "].DbType = DbType." + TypeName + ";");
            sb.AppendLine(spacer + "parameters[" + IndexPosition + "].Value = " + Value + ";");
            return sb.ToString();
        }

        public string GetDataAccessClassGetAllMethod(string ClassName, string TableName)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_ACCESS_CLASS_GETALL_METHOD_FILENAME);
            templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
            templatePlainText = templatePlainText.Replace("{TableName}", TableName);
            return templatePlainText;
        }

        public string GetDataAccessClassRemoveMethod(string ClassName, IGeneratorModel Model, string WhereCondition, string TableName, string PropertiesNames, string ParameterNames)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_DATA_ACCESS_CLASS_REMOVE_METHODS_FILENAME);
            templatePlainText = templatePlainText.Replace("{ClassName}", ClassName);
            templatePlainText = templatePlainText.Replace("{WhereCondition}", WhereCondition);
            templatePlainText = templatePlainText.Replace("{TableName}", TableName);
            templatePlainText = templatePlainText.Replace("{PropertiesNames}", PropertiesNames);
            templatePlainText = templatePlainText.Replace("{ParametersNames}", ParameterNames);
            return templatePlainText;
        }

        public string GetProjectSourceCode(IGeneratorModel Model)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_PROJECT_FILENAME);
            StringBuilder sb = new StringBuilder();
            foreach (IGeneratorClass c in Model.Classes)
            {
                sb.Append("<Compile Include=\"" + c.ClassName + ".cs\" />");
                sb.Append("<Compile Include=\"" + c.ClassName + "Data.cs\" />");
                sb.Append("<Compile Include=\"" + c.ClassName + "DAL.cs\" />");
            }
            
            templatePlainText = templatePlainText.Replace("{IncludedClasses}", sb.ToString());
            return templatePlainText;
        }
        public string GetProjectSampleSourceCode(IGeneratorModel Model)
        {
            
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_PROJECT_SAMPLE_FILENAME);
            //StringBuilder sb = new StringBuilder();
            //foreach (IGeneratorClass c in Model.Classes)
            //{
            //    sb.Append("<Compile Include=\"" + c.ClassName + ".cs\" />");
            //    sb.Append("<Compile Include=\"" + c.ClassName + "Data.cs\" />");
            //    sb.Append("<Compile Include=\"" + c.ClassName + "DAL.cs\" />");
            //}

            templatePlainText = templatePlainText.Replace("{NameSpace}", Model.NameSpace);
            return templatePlainText;
        }

        public string GetSampleProject_ProgramCS_SourceCode(IGeneratorModel Model)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_PROJECT_SAMPLE_PROGRAM_CS_FILENAME);
            return templatePlainText;
        }
        public string GetSampleProject_MainFormCS_SourceCode(IGeneratorModel Model)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_PROJECT_SAMPLE_MAINFORM_CS_FILENAME);
            return templatePlainText;
        }
        public string GetSampleProject_MainFormRESX_SourceCode(IGeneratorModel Model)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_PROJECT_SAMPLE_MAINFORM_RESX_FILENAME);
            return templatePlainText;
        }
        public string GetSampleProject_Solution_SourceCode(IGeneratorModel Model)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_PROJECT_SAMPLE_SOLUTION_FILENAME);
            templatePlainText = templatePlainText.Replace("{NameSpace}", Model.NameSpace);
            return templatePlainText;
        }

        public string GetAssemblyInfoSourceCode(IGeneratorModel Model)
        {
            string templatePlainText = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_ASSEMBLY_INFO_FILENAME);
            StringBuilder sb = new StringBuilder();
            //templatePlainText = templatePlainText.Replace("{IncludedClasses}", sb.ToString());
            
            return templatePlainText;   
        }

        public string GetDataBasesDropStoredProcedure(IGeneratorClass generatorClass, Implementations.CodeGenerator.StoredProcedureType SpType)
        {
            string procedureTermination = "";
            switch (SpType)
            {
                case Implementations.CodeGenerator.StoredProcedureType.Insert:
                    {
                        procedureTermination = "_ADD";
                        break;
                    }
                case Implementations.CodeGenerator.StoredProcedureType.Update:
                    {
                        procedureTermination = "_UPD";
                        break;
                    }
                case Implementations.CodeGenerator.StoredProcedureType.Delete:
                    {
                        procedureTermination = "_DEL";
                        break;
                    }
                default:
                    throw new ArgumentOutOfRangeException("SpType");
            }
            return
                        String.Format(
                            "IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = '{0}')" +
                            Environment.NewLine + "drop procedure {0}" + Environment.NewLine,
                            "SP_" + generatorClass.DatabaseTable.TableName + procedureTermination);
        }

        public string GetDataBasesInsertStoredProcedure(IGeneratorClass generatorClass, IGeneratorModel model)
        {
            string res = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_STORED_PROCEDURE_INSERT);
            //{TableName}{ParametersDeclaration}{ColumnsList}{ParameterNames}{IdentityRecover}
            //StoredProcedureInsertSqlServer.tpl
            String sbColumnsList ="", sbParameterNames="", sbParameterDeclaration = "", sbIdentityRecover="";
            foreach (IDatabaseColumn databaseColumn in generatorClass.DatabaseTable.Columns)
            {
                //exclude the column name if it's an identity column
                if(!generatorClass.DatabaseTable.HaveIdentityColumn || (!databaseColumn.IsIdentity && generatorClass.DatabaseTable.HaveIdentityColumn))
                {
                    sbColumnsList += databaseColumn.ColumnName + ", ";
                    sbParameterNames += "@" + databaseColumn.ColumnName + ", ";
                    sbParameterDeclaration += "@" + databaseColumn.ColumnName + " " + databaseColumn.DataBaseTypeName;
                    if (databaseColumn.MaxLength != "" && databaseColumn.MaxLength != "-1" && databaseColumn.DataBaseTypeName.ToLower() != "text")
                        sbParameterDeclaration += "(" + databaseColumn.MaxLength + "), ";
                    else if (databaseColumn.Precision != "" && databaseColumn.Scale != "" && databaseColumn.DataType == typeof(Decimal))
                        sbParameterDeclaration += "(" + databaseColumn.Precision + "," + databaseColumn.Scale + "), ";
                    else
                        sbParameterDeclaration += ", ";
                }
                else
                {
                    sbIdentityRecover = " set @" + databaseColumn.ColumnName + " = @@identity;";
                    sbParameterDeclaration += "@" + databaseColumn.ColumnName + " " +databaseColumn.DataBaseTypeName + " out, ";
                }
            }
            sbColumnsList = sbColumnsList.Substring(0,sbColumnsList.Length - 2);
            sbParameterNames= sbParameterNames.Substring(0,sbParameterNames.Length - 2);
            sbParameterDeclaration= sbParameterDeclaration.Substring(0,sbParameterDeclaration.Length - 2);
            res = res.Replace("{TableName}", generatorClass.DatabaseTable.TableName);
            res = res.Replace("{ParametersDeclaration}", sbParameterDeclaration);
            res = res.Replace("{ColumnsList}", sbColumnsList);
            res = res.Replace("{ParameterNames}", sbParameterNames);
            res = res.Replace("{IdentityRecover}", sbIdentityRecover);
            return res;
        }

        public string GettDataBasesUpdateStoredProcedure(IGeneratorClass generatorClass, IGeneratorModel model)
        {
            string res = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_STORED_PROCEDURE_UPDATE);

            string sbParametersAssignation = "", sbParameterDeclaration = "", sbPrimaryKeyIdentificator = "";

            foreach (IDatabaseColumn databaseColumn in generatorClass.DatabaseTable.Columns)
            {
                if(!databaseColumn.IsIdentity)
                    sbParametersAssignation +=  databaseColumn.ColumnName + " = @" + databaseColumn.ColumnName + ", ";
                
                sbParameterDeclaration += "@" + databaseColumn.ColumnName + " " + databaseColumn.DataBaseTypeName;
                if (databaseColumn.MaxLength != "" && databaseColumn.MaxLength != "-1" && databaseColumn.DataBaseTypeName.ToLower() != "text")
                    sbParameterDeclaration += "(" + databaseColumn.MaxLength + "), ";
                else if (databaseColumn.Precision != "" && databaseColumn.Scale != "" && databaseColumn.DataType == typeof(Decimal))
                    sbParameterDeclaration += "(" + databaseColumn.Precision + "," + databaseColumn.Scale + "), ";
                else
                    sbParameterDeclaration += ", ";
            }
            foreach (IDatabaseColumn databaseColumn in generatorClass.DatabaseTable.PrimaryKey)
                sbPrimaryKeyIdentificator += " " + databaseColumn.ColumnName + " = @" + databaseColumn.ColumnName + " AND";

            sbParametersAssignation = sbParametersAssignation.Substring(0, sbParametersAssignation.Length - 2);
            sbParameterDeclaration = sbParameterDeclaration.Substring(0, sbParameterDeclaration.Length - 2);
            
            if (sbPrimaryKeyIdentificator.Length>0)
                sbPrimaryKeyIdentificator = sbPrimaryKeyIdentificator.Substring(0, sbPrimaryKeyIdentificator.Length - 4);

            res = res.Replace("{TableName}", generatorClass.DatabaseTable.TableName);
            res = res.Replace("{ParametersDeclaration}", sbParameterDeclaration);
            res = res.Replace("{ParametersAssignation}", sbParametersAssignation);
            res = res.Replace("{PrimaryKeyIdentificator}", sbPrimaryKeyIdentificator);

            return res;
        }

        public string GettDataBasesDeleteStoredProcedure(IGeneratorClass generatorClass, IGeneratorModel model)
        {
            string res = Common.Templating.GetTemplatePlainText(CONST_TEMPLATE_STORED_PROCEDURE_DELETE);
            string sParametersDeclaration = "", sWhereCondition = "";
            if (generatorClass.DatabaseTable.HaveIdentityColumn)
            {
                //This loop happens just one time
                foreach (IDatabaseColumn databaseColumn in generatorClass.DatabaseTable.Columns.Where(r => r.IsIdentity))
                {
                    sParametersDeclaration += "@" + databaseColumn.ColumnName + " " + databaseColumn.DataBaseTypeName;
                        
                    if (databaseColumn.MaxLength != "" && databaseColumn.MaxLength != "-1" && databaseColumn.DataBaseTypeName.ToLower() != "text")
                        sParametersDeclaration += "(" + databaseColumn.MaxLength + ")";

                    sWhereCondition += databaseColumn.ColumnName + " = @" + databaseColumn.ColumnName;

                }
            }
            else if(generatorClass.DatabaseTable.PrimaryKey.Count>0)
            {
                foreach (IDatabaseColumn databaseColumn in generatorClass.DatabaseTable.PrimaryKey)
                {
                    sParametersDeclaration += "@" + databaseColumn.ColumnName + " " + databaseColumn.DataBaseTypeName + ", ";

                    if (databaseColumn.MaxLength != "" && databaseColumn.MaxLength != "-1" && databaseColumn.DataBaseTypeName.ToLower() != "text")
                        sParametersDeclaration += "(" + databaseColumn.MaxLength + "), ";
                    sWhereCondition += databaseColumn.ColumnName + " = @" + databaseColumn.ColumnName + ", ";
                }
                sParametersDeclaration = sParametersDeclaration.Substring(0, sParametersDeclaration.Length - 2);
                sWhereCondition = sWhereCondition.Substring(0, sWhereCondition.Length - 2);
            }
            else
            {
                res = "//The table " + generatorClass.DatabaseTable.TableName +
                      " does not have Primary key or Identity Column. No delete method is provided for that case.";
            }

            res = res.Replace("{TableName}", generatorClass.DatabaseTable.TableName);
            res = res.Replace("{WhereCondition}", sWhereCondition);
            res = res.Replace("{ParametersDeclaration}", sParametersDeclaration);

            return res;
        }
    }
}
